package cn.com.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 数据库搜索
 *
 * @author LiuGuodong
 * @date 2024/10/11
 */
public class DatabaseSearch {

    // 数据库连接信息
    private static final String HOST = "58.48.42.246";
    private static final String PORT = "32176";
    private static final String DB_NAME = "bss";
    private static final String USER = "test";
    private static final String PASSWORD = "vx7RsHeDXMj9XSXpV^Qe";
    // 需要搜索的关键字
    private static final String KEYWORD = "daocloud";

    public static void main(String[] args) {
        Map<String, List<String>> result = searchDatabaseForKeyword(KEYWORD);
        printResult(result);
        printQueryStatement(result);
    }


    /**
     * 打印查询陈述
     *
     * @param searchCriteria 搜索标准
     */
    public static void printQueryStatement(Map<String, List<String>> searchCriteria) {
        List<String> result = new ArrayList<>(searchCriteria.size());

        // 根据 searchCriteria 逐个表进行查询
        for (Map.Entry<String, List<String>> entry : searchCriteria.entrySet()) {
            String table = entry.getKey();
            List<String> columns = entry.getValue();
            generateQueryStatements(table, columns, result);
        }

        // 打印查询结果
        result.stream().sorted().forEach(System.out::println);
    }

    /**
     * 生成查询
     *
     * @param table   表
     * @param columns 列
     * @param result  结果
     */
    private static void generateQueryStatements(String table, List<String> columns, List<String> result) {
        //构造删除语句
        StringBuilder delete = new StringBuilder("DELETE FROM ").append(table).append(" WHERE ");
        // 构造查询语句
        StringBuilder query = new StringBuilder("SELECT * FROM ").append(table).append(" WHERE ");
        for (int i = 0; i < columns.size(); i++) {
            query.append(columns.get(i)).append(" LIKE '%").append(KEYWORD).append("%'");
            delete.append(columns.get(i)).append(" LIKE '%").append(KEYWORD).append("%'");
            if (i < columns.size() - 1) {
                delete.append(" OR ");
                query.append(" OR ");
            }
        }
        result.add(delete.append(";").toString());
        result.add(query.append(";").toString());
    }

    /**
     * 在数据库的所有表中搜索包含指定关键字的数据
     *
     * @param keyword 要搜索的关键字
     * @return 返回包含关键字的表名及字段名的映射
     */
    public static Map<String, List<String>> searchDatabaseForKeyword(String keyword) {
        Map<String, List<String>> result = new ConcurrentHashMap<>(200);

        try (Connection conn = createConnection();
             Statement stmt = conn.createStatement();
             ResultSet tables = stmt.executeQuery("SHOW TABLES")) {
            AtomicInteger count = new AtomicInteger();
            int fetchSize = getTableCount(stmt);
            // 遍历所有表
            while (tables.next()) {
                String tableName = tables.getString(1);
                searchTableForKeyword(stmt, tableName, keyword, result);
                //进度条百分比显示
                System.out.println("已搜索 " + count.getAndIncrement() + " 个表" + "当前完成度：" + (count.get() * 100 / fetchSize) + "%");
                ////开启异步
                //CompletableFutureUtil.runAsync(() -> {
                //});
                //Thread.sleep(100);
            }

        } catch (Exception e) {
            throw new RuntimeException("数据库操作失败", e);
        }
        return result;
    }


    /**
     * 获取数据库中表的个数
     *
     * @param stmt SQL 语句对象
     * @return 表的数量
     * @throws SQLException 如果查询失败则抛出异常
     */
    public static int getTableCount(Statement stmt) throws SQLException {
        ResultSet tables = stmt.executeQuery("SHOW TABLES");
        int tableCount = 0;
        while (tables.next()) {
            tableCount++;
        }
        return tableCount;
    }

    /**
     * 创建数据库连接
     *
     * @return 返回数据库连接
     * @throws SQLException 如果连接失败则抛出异常
     */
    private static Connection createConnection() throws SQLException, ClassNotFoundException {
        Class.forName("org.mariadb.jdbc.Driver");
        String url = String.format("jdbc:mariadb://%s:%s/%s?sslMode=required&useUnicode=true&autoReconnect=true&characterEncoding=UTF-8&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%%2B8",
                HOST, PORT, DB_NAME);
        return DriverManager.getConnection(url, USER, PASSWORD);
    }

    /**
     * 在指定表中搜索包含关键字的行
     *
     * @param stmt    SQL 语句对象
     * @param table   表名
     * @param keyword 关键字
     * @param result  存储搜索结果的映射
     */
    private static void searchTableForKeyword(Statement stmt, String table, String keyword, Map<String, List<String>> result) {
        String query = "SELECT * FROM " + table;
        try {
            try (ResultSet rows = stmt.executeQuery(query)) {

                while (rows.next()) {
                    List<String> matchingColumns = new ArrayList<>();

                    for (int i = 1; i <= rows.getMetaData().getColumnCount(); i++) {
                        String colData = rows.getString(i);

                        // 检查数据是否包含关键字
                        if (colData != null && colData.contains(keyword)) {
                            matchingColumns.add(rows.getMetaData().getColumnName(i));
                        }
                    }

                    // 如果找到匹配项，添加到结果
                    if (!matchingColumns.isEmpty()) {
                        result.computeIfAbsent(table, k -> new ArrayList<>()).addAll(matchingColumns);
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("查询表 " + table + " 失败");
        }
    }

    /**
     * 打印搜索结果
     *
     * @param result 搜索结果，包含表名和字段名
     */
    private static void printResult(Map<String, List<String>> result) {
        result.forEach((table, columns) -> System.out.println("表名: " + table + " 包含关键字的字段: " + columns));
    }
}
